import sqlite3

DATABASE = 'home.db'

def create_tables_and_init_data():
    conn = sqlite3.connect(DATABASE)
    cursor = conn.cursor()

    cursor.execute('''
    CREATE TABLE IF NOT EXISTS users (
        user_id INTEGER PRIMARY KEY AUTOINCREMENT,
        username TEXT NOT NULL UNIQUE,
        password TEXT NOT NULL
    )
    ''')

    cursor.execute('''
    CREATE TABLE IF NOT EXISTS book (
        book_id INTEGER PRIMARY KEY AUTOINCREMENT,
        book_name TEXT NOT NULL,
        author_name TEXT,
        publisher_name TEXT,
        price REAL,
        page_number INTEGER,
        isbn_number TEXT,
        publish_date TEXT,
        img_url TEXT,
        img1_url TEXT,
        img2_url TEXT,
        img3_url TEXT,
        img4_url TEXT,
        uploader_name TEXT
    )
    ''')

    # 插入默认用户
    cursor.execute('''
    INSERT OR IGNORE INTO users (username, password) VALUES (?, ?)
    ''', ('admin', '123456'))

    cursor.executemany('''
    INSERT INTO book (book_name, author_name, publisher_name, price, page_number, isbn_number, publish_date, img_url, img1_url, img2_url, img3_url, img4_url, uploader_name)
    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    ''', [
        ('小学语文课本一年级上册', '人民教育出版社', '人民教育出版社', 25.0, 120, '9787107310001', '2020-09-01', '', '', '', '', '', 'wupicheng'),
        ('小学数学课本二年级下册', '陈立群', '人民教育出版社', 30.0, 110, '9787107310002', '2021-03-01', '', '', '', '', '', 'wupicheng'),
        ('小学英语课本三年级上册', '李华', '外语教学与研究出版社', 28.0, 100, '9787560010003', '2019-09-01', '', '', '', '', '', 'wupicheng'),
    ])

    conn.commit()
    cursor.close()
    conn.close()
    print("数据库建表及初始化数据完成。")

if __name__ == '__main__':
    create_tables_and_init_data()
